"""
Copyright 2012 Highline Public Schools

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
"""
import logging, sqlite3, os, ConfigParser
from datetime import datetime

class crdc(object):
	'''
	classdocs
	'''
	PART_SCHOOL1 = 'school1'
	PART_SCHOOL2 = 'school2'
	
	ELEMENT_HEADER = 'header'
	ELEMENT_PART = 'part'
	ELEMENT_NAME = 'element_name'
	ELEMENT_DESCRIPTION = 'description'
	ELEMENT_TOPIC = 'topic'
	ELEMENT_OPERATION = 'operation'
	ELEMENT_RACE = 'race'
	ELEMENT_SEX = 'sex'
	ELEMENT_IDEA = 'idea'
	ELEMENT_504 = 'is504'
	ELEMENT_LEP = 'lep'
	ELEMENT_GRADE_LOW = 'grade_low'
	ELEMENT_GRADE_HIGH = 'grade_high'
	ELEMENT_LITERAL = 'literal'
	ELEMENT_SKIP = 'skip'
	ELEMENT_COLNUM = 'colnum'
	
	ELEMENT_STUDENT_ID = 'student_id'
	ELEMENT_SCHOOL = 'school'
	ELEMENT_PRE_K = 'pre_k'
	ELEMENT_GIFTED = 'gifted'
	ELEMENT_ELL = 'lep_enrolled'
	ELEMENT_ALGEBRA_1_TAKEN = 'algebra_1_taken'
	ELEMENT_ALGEBRA_2_TAKEN = 'algebra_2_taken'
	ELEMENT_GEOMETRY_TAKEN = 'geometry_taken'
	ELEMENT_ADVANCED_MATH_TAKEN = 'advanced_math_taken'
	ELEMENT_CALCULUS_TAKEN = 'calculus_taken'
	ELEMENT_BIOLOGY_TAKEN = 'biology_taken'
	ELEMENT_CHEMISTRY_TAKEN = 'chemistry_taken'
	ELEMENT_PHYSICS_TAKEN = 'physics_taken'
	ELEMENT_IB = 'ib'
	ELEMENT_GRADE = 'grade'
	ELEMENT_AP_ANY_TAKEN = 'ap_any_taken'
	ELEMENT_AP_MATH_TAKEN = 'ap_math_taken'
	ELEMENT_AP_SCIENCE_TAKEN = 'ap_science_taken'
	ELEMENT_AP_FOREIGN_LANG_TAKEN = 'ap_foreign_lang_taken'
	ELEMENT_AP_OTHER_TAKEN = 'ap_other_taken'
	ELEMENT_GED_PREP = 'ged_prep'
	ELEMENT_GED_TAKEN = 'ged_taken'
	ELEMENT_ALGEBRA_1_PASSED = 'algebra_1_passed'
	ELEMENT_SAT_ACT = 'sat_act'
	ELEMENT_AP_EXAM_TAKEN = 'ap_exam_taken'
	ELEMENT_AP_EXAM_PASSED = 'ap_exam_passed'
	ELEMENT_AP_EXAM_PASSED_ALL = 'ap_exam_passed_all'
	ELEMENT_AP_EXAM_PASSED_SOME = 'ap_exam_passed_some'
	ELEMENT_AP_EXAM_PASSED_NONE = 'ap_exam_passed_none'
	ELEMENT_RETAINED = 'retained'
	ELEMENT_SUSP_OUT = 'susp_out'
	ELEMENT_SUSP_IN = 'susp_in'
	ELEMENT_CORPORAL = 'corporal'
	ELEMENT_EXPULSION_W = 'expulsion_w'
	ELEMENT_EXPULSION_WO = 'expulsion_wo'
	ELEMENT_EXPULSION_ZERO = 'expulsion_zero'
	ELEMENT_LAW = 'law'
	ELEMENT_ARREST = 'arrest'

	COLCOUNT_SCHOOL1 = 518
	COLCOUNT_SCHOOL2 = 1170

	parts = []
	
	def __init__(self,options_file):
		'''
		Constructor
		'''
		self.logger = logging.getLogger(__name__)
		stderr = logging.StreamHandler()
		self.logger.addHandler(stderr)
		ini = ConfigParser.RawConfigParser()
		inipath = options_file
		inifile = open(inipath,'r')
		ini.readfp(inifile)
		self.configs = dict()
		configs_expected = dict({'directories': ['config_dir','data_dir'], 
						'data': ['leaid']})
		for section in configs_expected.iterkeys():
			for option in configs_expected[section]:
				if ini.has_option(section, option):
					self.configs[option] = ini.get(section,option).rstrip('/')
					self.logger.debug('option {}={}'.format(option,self.configs[option]))
				else:
					raise Exception('"{}" not set in options file {}'.format(option,inipath))
		inifile.close()
		self.cxn = None
		self.element_school_indexes = {
			self.ELEMENT_HEADER: 0,
			self.ELEMENT_NAME: None,
			self.PART_SCHOOL1: None,
			self.PART_SCHOOL2: None,
			self.ELEMENT_DESCRIPTION: None,
			self.ELEMENT_TOPIC: None,
			self.ELEMENT_OPERATION: None,
			self.ELEMENT_RACE: None,
			self.ELEMENT_SEX: None,
			self.ELEMENT_IDEA: None,
			self.ELEMENT_504: None,
			self.ELEMENT_LEP: None,
			self.ELEMENT_GRADE_LOW: None,
			self.ELEMENT_GRADE_HIGH: None,
			self.ELEMENT_LITERAL: None,
			self.ELEMENT_SKIP: None,
			self.ELEMENT_COLNUM: None
		}
		self.detail_school_indexes = {
			self.ELEMENT_STUDENT_ID: None,
			self.ELEMENT_PART: None,
			self.ELEMENT_SCHOOL: None,
			self.ELEMENT_RACE: None,
			self.ELEMENT_SEX: None,
			self.ELEMENT_IDEA: None,
			self.ELEMENT_504: None,
			self.ELEMENT_LEP: None,
			self.ELEMENT_GRADE: None,
			self.ELEMENT_PRE_K: None,
			self.ELEMENT_GIFTED: None,
			self.ELEMENT_ELL: None,
			self.ELEMENT_ALGEBRA_1_TAKEN: None,
			self.ELEMENT_ALGEBRA_2_TAKEN: None,
			self.ELEMENT_GEOMETRY_TAKEN: None,
			self.ELEMENT_ADVANCED_MATH_TAKEN: None,
			self.ELEMENT_CALCULUS_TAKEN: None,
			self.ELEMENT_BIOLOGY_TAKEN: None,
			self.ELEMENT_CHEMISTRY_TAKEN: None,
			self.ELEMENT_PHYSICS_TAKEN: None,
			self.ELEMENT_IB: None,
			self.ELEMENT_AP_ANY_TAKEN: None,
			self.ELEMENT_AP_MATH_TAKEN: None,
			self.ELEMENT_AP_SCIENCE_TAKEN: None,
			self.ELEMENT_AP_FOREIGN_LANG_TAKEN: None,
			self.ELEMENT_AP_OTHER_TAKEN: None,
			self.ELEMENT_ALGEBRA_1_PASSED: None,
			self.ELEMENT_SAT_ACT: None,
			self.ELEMENT_AP_EXAM_TAKEN: None,
			self.ELEMENT_AP_EXAM_PASSED: None,
			self.ELEMENT_RETAINED: None,
			self.ELEMENT_SUSP_OUT: None,
			self.ELEMENT_SUSP_IN: None,
			self.ELEMENT_CORPORAL: None,
			self.ELEMENT_EXPULSION_W: None,
			self.ELEMENT_EXPULSION_WO: None,
			self.ELEMENT_EXPULSION_ZERO: None,
			self.ELEMENT_LAW: None,
			self.ELEMENT_ARREST: None
		 }
		self.integer_columns = [self.ELEMENT_STUDENT_ID,
			self.ELEMENT_GRADE,
			self.ELEMENT_GRADE_LOW,
			self.ELEMENT_GRADE_HIGH,
			self.ELEMENT_AP_FOREIGN_LANG_TAKEN,
			self.ELEMENT_AP_MATH_TAKEN,
			self.ELEMENT_AP_OTHER_TAKEN,
			self.ELEMENT_AP_SCIENCE_TAKEN,
			self.ELEMENT_AP_ANY_TAKEN,
			self.ELEMENT_AP_EXAM_PASSED,
			self.ELEMENT_AP_EXAM_TAKEN,
			self.ELEMENT_EXPULSION_W,
			self.ELEMENT_EXPULSION_WO,
			self.ELEMENT_SUSP_IN,
			self.ELEMENT_SUSP_OUT,
			self.ELEMENT_CORPORAL,
			self.ELEMENT_ARREST,
			self.ELEMENT_LAW
			]

		self.element_config = dict({self.PART_SCHOOL1: dict(), self.PART_SCHOOL2: dict()})
		self.data_final = dict({self.PART_SCHOOL1: dict(), self.PART_SCHOOL2: dict()})
		self.schools = dict()

	def cxn_get(self):
		if not self.cxn:
			db_path = '{}/crdc.sqlite'.format(self.configs['data_dir'])
			self.cxn = sqlite3.connect(db_path)
			sql = 'select student_id from school limit 1;'
			cur = self.cxn.cursor()
			try:
				cur.execute(sql)
			except sqlite3.OperationalError:
				self.__db_init(self.cxn)
		return self.cxn
		
	
	def __db_init(self,cxn):
		indexes = self.detail_school_indexes
		sql = 'create table school ('
		for detail_index_key in indexes.iterkeys():
			if detail_index_key in self.integer_columns:
				datatype = 'integer'
			else:
				datatype = 'text'
			sql += '{} {}, '.format(detail_index_key,datatype)
		sql = sql.rstrip(' ,')
		sql += ');'
		cur = cxn.cursor()
		cur.execute(sql)
		sql = 'create unique index ix_stu_school on school (student_id, school);'
		cur.execute(sql)
		sql = 'create unique index ix_school_stu on school (school, student_id);'
		cur.execute(sql)
		cxn.commit()
	
	def detail_load(self):
		"Load the data file into the local database table, overwriting the table."
		detail_indexes = self.detail_school_indexes
		sql_columns = ''
		sql_values = ''
		for detail_index_key in detail_indexes.iterkeys():
			sql_columns += '{}, '.format(detail_index_key)
			sql_values += ":{}, ".format(detail_index_key)
		sql_columns = sql_columns.rstrip(', ')
		sql_values = sql_values.rstrip(', ')
		sql_insert = 'insert into school ({}) values ({});'.format(sql_columns,sql_values)
		self.logger.debug(sql_insert)
		filepath = '{}/detail.txt'.format(self.configs['data_dir'])
		detail_file = open(filepath,'r')
		linenum = 0
		for line in detail_file:
			line = line.rstrip('\r\n')
			linenum = linenum + 1
			parsed = line.split('\t')
			if linenum == 1:
				fieldnum = 0
				while fieldnum <= len(parsed) - 1:
					field = parsed[fieldnum].lower()
					if field in self.detail_school_indexes:
						if detail_indexes[field] != None:
							raise Exception('Element column "{}" defined more than once.'.format(field))
						detail_indexes[field] = fieldnum
						self.logger.debug('Index of {} is {}.'.format(field,fieldnum))
					else:
						self.logger.debug('Ignoring field {} at {}.'.format(field,fieldnum))
					fieldnum += 1
				for detail_index_key in detail_indexes.iterkeys():
					if detail_indexes[detail_index_key] == None:
						raise Exception('The header row lacks the required column "{}"'.format(detail_index_key))
				header_found = True
				cxn = self.cxn_get()
				cur = cxn.cursor()
				cur.execute('delete from school;')
			elif header_found:
				sql_params = dict()
				for detail_index_key in detail_indexes.iterkeys():
					sql_params[detail_index_key] = parsed[detail_indexes[detail_index_key]]
				cur.execute(sql_insert, sql_params)
		detail_file.close()
		if cxn:
			cxn.commit()
		self.logger.debug('loaded {} students'.format(linenum))
	
	def __element_config_indexes_load(self):
		"Load the school element config file and find the indexes of the header row."
		element_indexes = self.element_school_indexes
		filepath = '{}/elements_school.txt'.format(self.configs['config_dir'])
		element_file = open(filepath,'r')
		linenum = 0
		header_found = False
		for line in element_file:
			line = line.rstrip()
			linenum = linenum + 1
			#self.logger.debug('line %d', linenum)
			parsed = line.split('\t')
			if parsed[0].lower() == self.ELEMENT_HEADER:
				header_found = True
				fieldnum = 1
				while fieldnum <= len(parsed) - 1:
					field = parsed[fieldnum].lower()
					if field in element_indexes:
						if element_indexes[field] != None:
							raise Exception('Element column "{}" defined more than once.'.format(field))
						element_indexes[field] = fieldnum
						self.logger.debug('Index of {} is {}.'.format(field,fieldnum))
					else:
						self.logger.debug('Ignoring field {} at {}.'.format(field,fieldnum))
					fieldnum = fieldnum + 1
				for element_index_key in element_indexes.iterkeys():
					if element_indexes[element_index_key] == None:
						raise Exception('The header row lacks the required column "{}"'.format(element_index_key))
				break
		element_file.close()
		if not header_found:
			raise Exception('No header row found in element config file.')
		self.logger.debug('Found header at line %d', linenum)
		return linenum
		
	def element_config_load(self):
		"Load the school element config file."
		header_line = self.__element_config_indexes_load()
		element_indexes = self.element_school_indexes
		filepath = '{}/elements_school.txt'.format(self.configs['config_dir'])
		element_file = open(filepath,'r')
		for part in self.parts:
			self.logger.debug('Loading config for part {}'.format(part))
			element_file.seek(0)
			linenum = 0
			for line in element_file:
				linenum += 1
				if linenum <= header_line:
					continue
				line = line.rstrip()
				parsed = line.split('\t')
				#self.logger.debug('line {}, name="{}"'.format(linenum,parsed[element_indexes[self.ELEMENT_NAME]]))
				if ((part == self.PART_SCHOOL1 and parsed[element_indexes[self.PART_SCHOOL1]].lower() == 'y')
				 	or (part == self.PART_SCHOOL2 and parsed[element_indexes[self.PART_SCHOOL2]].lower() == 'y')):
					config = dict()	
					for element_index_key in element_indexes.iterkeys():
						config[element_index_key] = parsed[element_indexes[element_index_key]]
						colnum = int(parsed[element_indexes[self.ELEMENT_COLNUM]])
					self.element_config[part][colnum] = config
			config_len = len(self.element_config[part])
			self.logger.debug('config length = {}'.format(config_len))
			if part == self.PART_SCHOOL1:
				colcount = self.COLCOUNT_SCHOOL1
			elif part == self.PART_SCHOOL2:
				colcount = self.COLCOUNT_SCHOOL2
			for col in range(1,colcount + 1):
				if not (col in self.element_config[part]):
					raise Exception('Definition for column number {} missing from elements file {}.'.format(col,filepath)) 
		element_file.close()
		
	def __filenames_id(self):
		now = datetime.now()
		return now.strftime('%j%H%M')
	
	def filenames_official(self):
		"Return dict of official CRDC filenames"
		names = dict()
		for part in self.parts:
			partnum = part[6:7]
			filename = '{}/{}SCH{}{}.csv'.format(self.configs['data_dir'],
													self.configs['leaid'],
													partnum,
													self.__filenames_id())
			names[part] = filename
		return names
		
	
	def final_values_build(self):
		"Build array of values ready for ouptput."
		cxn = self.cxn_get()
		cxn.row_factory = sqlite3.Row
		cur = cxn.cursor()
		for part in self.parts:
			if part == self.PART_SCHOOL1:
				colcount = self.COLCOUNT_SCHOOL1
				sqlpart = "and {} = '{}' ".format(self.ELEMENT_PART, part)
			elif part == self.PART_SCHOOL2:
				colcount = self.COLCOUNT_SCHOOL2
				sqlpart = ''
			self.logger.info('final_values_build: part {}'.format(part))
			for schoolnum in self.schools.iterkeys():
				self.logger.info('final_values_build: {}'.format(self.schools[schoolnum]))
				self.data_final[part][schoolnum] = dict()
				for final_colnum in range(1,colcount + 1):
					config = self.element_config[part][final_colnum]
					if config[self.ELEMENT_SKIP].lower() == 'y':
						self.data_final[part][schoolnum][final_colnum] = ''
					elif config[self.ELEMENT_OPERATION].lower() == 'literal':
						self.data_final[part][schoolnum][final_colnum] = config[self.ELEMENT_LITERAL]
					elif (config[self.ELEMENT_OPERATION].lower() in ['count',
																	'count_0',
																	'count_1',
																	'count_1_plus',
																	'count_2_plus', 
																	'bool', 
																	'invbool',
																	'sum']):
						if (config[self.ELEMENT_OPERATION].lower() in ['count',
																	'count_0',
																	'count_1',
																	'count_1_plus', 
																	'count_2_plus',
																	'bool', 
																	'invbool']):
							select_content = 'count(student_id)'
						elif config[self.ELEMENT_OPERATION].lower() == 'sum':
							select_content = 'sum({})'.format(config[self.ELEMENT_TOPIC])
						else:
							raise Exception ('Unexpected operation (bug in the program)')
						sql = "select {} as col1 from school where school = :{} {}".format(select_content,self.ELEMENT_SCHOOL,sqlpart)
						sql_params = dict()
						sql_params[self.ELEMENT_SCHOOL] = schoolnum
						wheres = [self.ELEMENT_RACE,
								self.ELEMENT_SEX,
								self.ELEMENT_IDEA,
								self.ELEMENT_504,
								self.ELEMENT_LEP]
						for where in wheres:
							if config[where] != '':
								sql += 'and {} = :{} '.format(where,where)
								sql_params[where] = config[where].upper()
						if config[self.ELEMENT_GRADE_LOW] != '':
							sql += 'and {} between :{} and :{} '.format(self.ELEMENT_GRADE,self.ELEMENT_GRADE_LOW,self.ELEMENT_GRADE_HIGH)
							sql_params[self.ELEMENT_GRADE_LOW] = config[self.ELEMENT_GRADE_LOW]
							sql_params[self.ELEMENT_GRADE_HIGH] = config[self.ELEMENT_GRADE_HIGH]
						wheres = []
						wheres = [self.ELEMENT_GIFTED,
								self.ELEMENT_PRE_K,
								self.ELEMENT_ELL,
								self.ELEMENT_RETAINED,
								self.ELEMENT_ALGEBRA_1_TAKEN,
								self.ELEMENT_ALGEBRA_2_TAKEN,
								self.ELEMENT_GEOMETRY_TAKEN,
								self.ELEMENT_ADVANCED_MATH_TAKEN,
								self.ELEMENT_CALCULUS_TAKEN,
								self.ELEMENT_BIOLOGY_TAKEN,
								self.ELEMENT_CHEMISTRY_TAKEN,
								self.ELEMENT_PHYSICS_TAKEN,
								self.ELEMENT_IB,
								self.ELEMENT_AP_MATH_TAKEN,
								self.ELEMENT_AP_SCIENCE_TAKEN,
								self.ELEMENT_AP_FOREIGN_LANG_TAKEN,
								self.ELEMENT_AP_OTHER_TAKEN,
								self.ELEMENT_AP_ANY_TAKEN,
								self.ELEMENT_ALGEBRA_1_PASSED,
								self.ELEMENT_SAT_ACT,
								self.ELEMENT_SUSP_OUT,
								self.ELEMENT_SUSP_IN,
								self.ELEMENT_CORPORAL,
								self.ELEMENT_EXPULSION_W,
								self.ELEMENT_EXPULSION_WO,
								self.ELEMENT_EXPULSION_ZERO,
								self.ELEMENT_LAW,
								self.ELEMENT_ARREST]
						for where in wheres:
							if config[self.ELEMENT_TOPIC] == where:
								if where in self.integer_columns:
									sql += 'and {} > 0 '.format(where)
								else:
									sql += "and {} = 'Y' ".format(where)
						if config[self.ELEMENT_OPERATION].lower() in ['count_0','count_1','count_1_plus','count_2_plus']:
							compare_to_number = config[self.ELEMENT_OPERATION].lower()[6]
							if config[self.ELEMENT_OPERATION].lower() in ['count_0','count_1']:
								where_count_operator = '='
								if config[self.ELEMENT_OPERATION].lower() == 'count_0':
									if config[self.ELEMENT_TOPIC] == 'ap_exam_taken':
										sql += 'and ap_any_taken > 0 '
							elif config[self.ELEMENT_OPERATION].lower() in ['count_1_plus','count_2_plus']:
								where_count_operator = '>='
							sql += 'and {} {} {} '.format(config[self.ELEMENT_TOPIC],where_count_operator,compare_to_number)
						if (config[self.ELEMENT_OPERATION].lower() == 'count'
							and config[self.ELEMENT_TOPIC] in [self.ELEMENT_AP_EXAM_PASSED_ALL,
															self.ELEMENT_AP_EXAM_PASSED_NONE,
															self.ELEMENT_AP_EXAM_PASSED_SOME]):
							if config[self.ELEMENT_TOPIC] == self.ELEMENT_AP_EXAM_PASSED_ALL:
								sql += 'and {} > 0 and {} = {} '.format(self.ELEMENT_AP_EXAM_TAKEN,self.ELEMENT_AP_EXAM_PASSED,self.ELEMENT_AP_EXAM_TAKEN)
							if config[self.ELEMENT_TOPIC] == self.ELEMENT_AP_EXAM_PASSED_SOME:
								sql += 'and {} > 0 and {} > 0 and {} < {} '.format(self.ELEMENT_AP_EXAM_TAKEN,self.ELEMENT_AP_EXAM_PASSED,self.ELEMENT_AP_EXAM_PASSED,self.ELEMENT_AP_EXAM_TAKEN)
							if config[self.ELEMENT_TOPIC] == self.ELEMENT_AP_EXAM_PASSED_NONE:
								sql += 'and {} > 0 and {} = 0 '.format(self.ELEMENT_AP_EXAM_TAKEN,self.ELEMENT_AP_EXAM_PASSED)
						sql += ';'
						self.logger.debug('sql="{}".\nsql parameters="{}".'.format(sql,sql_params))
						try:
							cur.execute(sql,sql_params)
						except:
							self.logger.exception('topic={}, operation={}'.format(config[self.ELEMENT_TOPIC],config[self.ELEMENT_OPERATION]))
							self.logger.exception('sql="{}".\nsql parameters="{}".'.format(sql,sql_params))
							raise
						row = cur.fetchone()
						if config[self.ELEMENT_OPERATION].lower() in ['count',
																	'count_0',
																	'count_1',
																	'count_1_plus',
																	'count_2_plus',
																	'sum']:
							if len(row) == 0 or row['col1'] == None:
								self.data_final[part][schoolnum][final_colnum] = 0
							else:
								self.data_final[part][schoolnum][final_colnum] = int(row['col1'])
						elif config[self.ELEMENT_OPERATION].lower() == 'bool':
							if row['col1'] > 0:
								self.data_final[part][schoolnum][final_colnum] = 1
							else:
								self.data_final[part][schoolnum][final_colnum] = 0
						elif config[self.ELEMENT_OPERATION].lower() == 'invbool':
							if row['col1'] > 0:
								self.data_final[part][schoolnum][final_colnum] = 0
							else:
								self.data_final[part][schoolnum][final_colnum] = 1
					elif config[self.ELEMENT_OPERATION].lower() == 'code':
						if config[self.ELEMENT_TOPIC].lower() == 'leaid':
							self.data_final[part][schoolnum][final_colnum] = self.configs['leaid']
						elif config[self.ELEMENT_TOPIC].lower() == 'schid':
							self.data_final[part][schoolnum][final_colnum] = schoolnum
						elif config[self.ELEMENT_TOPIC].lower() == 'schoolname':
							self.data_final[part][schoolnum][final_colnum] = self.schools[schoolnum]
						elif config[self.ELEMENT_TOPIC].lower() == 'period':
							if part == self.PART_SCHOOL1:
								self.data_final[part][schoolnum][final_colnum] = 'A'
							if part == self.PART_SCHOOL2:
								self.data_final[part][schoolnum][final_colnum] = 'B'
					else:
						self.data_final[part][schoolnum][final_colnum] = ''
						self.logger.warning('Operation of type "{}" ignored in config file {}, column number {}.'.format(config[self.ELEMENT_OPERATION],part,final_colnum))
					if final_colnum in self.data_final[part][schoolnum]:
						self.logger.debug('column {}, {} = {}'.format(final_colnum,config[self.ELEMENT_NAME],self.data_final[part][schoolnum][final_colnum]))
					else:
						msg = 'Column {} not handled in {} {}'.format(final_colnum,part,self.schools[schoolnum]) 
						raise Exception(msg)
	
	def report(self):
		"Output results, both official format and human-readable."
		filenames = self.filenames_official()
		for part in self.parts:
			final = self.data_final[part]
			file_official = open(filenames[part],'w')
			file_human = open('{}/report_{}.txt'.format(self.configs['data_dir'],part),'w')
			schools = final.keys()
			schools.sort()
			for school in schools:
				official_line = ''
				cols = final[school].keys()
				cols.sort()
				for col in cols:
					tablename = self.element_config[part][col][self.ELEMENT_HEADER]
					schoolname = self.schools[school]
					elementname = self.element_config[part][col][self.ELEMENT_NAME]
					desc = self.element_config[part][col][self.ELEMENT_DESCRIPTION]
					value = str(final[school][col])
					human_line = "{}\t{}\t{}\t{}\t{}\t{}{}".format(schoolname,col,tablename,elementname,desc,value,os.linesep)
					official_line += '"{}",'.format(value)
					file_human.write(human_line)
				official_line = official_line.rstrip(',')
				official_line += "\r\n"
				file_official.write(official_line)
			file_human.close()
			file_official.close()
			self.validate_file(filenames[part])
	
	def schools_load(self):
		"load the schools file"
		schoolsfile = open('{}/schools.txt'.format(self.configs['config_dir']),'r')
		for line in schoolsfile:
			line = line.rstrip()
			parsed = line.split('\t')
			self.schools[parsed[0]] = parsed[1]
		self.logger.debug('loaded {} schools'.format(len(self.schools)))
		
	def validate_file(self, filename):
		"""Validate a CRDC flat file. The type of file is determined from the file name. 
		Validations include:
		   * the number of columns"""
		basename = os.path.basename(filename)
		tag = basename[7:11]
		if tag == 'SCH1':
			colcount = self.COLCOUNT_SCHOOL1
		elif tag == 'SCH2':
			colcount = self.COLCOUNT_SCHOOL2
		else:
			raise Exception("Unexpected filename format: {}".format(basename))
		file_official = open(filename,'r')
		rownum = 0
		valid = True
		for line in file_official:
			rownum += 1
			line = line.rstrip()
			parsed = line.split(',')
			colsfound = len(parsed)
			if colsfound != colcount:
				valid = False
				self.logger.warning('File {} row {}: found {} columns, expected {}'.format(filename,rownum,colsfound,colcount))
		self.logger.info('Test result for {} format: {}'.format(filename,valid))
		